package com.lumlord.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

/**
 * ClassName: ExcelAnalysisUtil<br/>
 * Description: Excel解析工具类 <br/>
 * date: 2019/6/12 0012 15:11<br/>
 *
 * @author SS<br />
 * @since JDK 1.8
 */
public final class ExcelAnalysisUtil {

	private static Workbook sheets;
	private static final int sheetNumber = 0;

	private ExcelAnalysisUtil() {
	}

	public static List<Map<String, Object>> excelToResults(byte[] file1, MultipartFile file2, int sheetNumber) {
		Workbook workbook = null;
		try {
			if (file1 != null) {
				ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(file1);
				workbook = WorkbookFactory.create(byteArrayInputStream);
			} else if (file2 != null) {
				workbook = WorkbookFactory.create(file2.getInputStream());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		Boolean flag = true;
		List<String> keys = new ArrayList<>();
		Map<String, Object> result = null;
		List<Map<String, Object>> results = new ArrayList<>();

		Sheet sheet = workbook.getSheetAt(sheetNumber);
		for (Row row : sheet) {
			result = new LinkedHashMap<>();

			result.put("rowNumber", row.getRowNum());

			if (flag) {
				for (Cell cell : row) {
					keys.add(cell.getStringCellValue());
				}
			} else {
				for (int i = 0; i < keys.size(); i++) {
					String key = keys.get(i);
					Cell cell = row.getCell(i);
					if(cell != null) {
						cell.setCellType(CellType.STRING);
						String stringCellValue = cell.getStringCellValue();
						result.put(key, stringCellValue);
					} else {
						continue;
					}
				}
			}

			if (!flag) {
				results.add(result);
			}

			flag = false;
		}

		return results;
	}


	/**
	 * 功能描述:               将excel内容转为ArrayList<LinkedHashMap<String, Object>> 结构
	 * excel文本格式要求:      最基础格式的excle文件
	 * 支持文件流对象数据类型 : byte[] || MultipartFile 文件对象形式 如果传递一种数据类型 则另一参数请传 NULL
	 *
	 * @param: byte[] file1 NULL
	 * @param: MultipartFile file2 NULL
	 * @return: ArrayList<LinkedHashMap < String, Object>>
	 * @auther: SS
	 * @date: 2019/6/11 0011 16:04
	 */
	@Deprecated
	public static ArrayList<LinkedHashMap<String, Object>> excelToHashMap(byte[] file1, MultipartFile file2) {

		try {
			if (file1 != null) {
				InputStream byteArrayInputStream = new ByteArrayInputStream(file1);
				sheets = WorkbookFactory.create(byteArrayInputStream);
			} else if (file2 != null) {
				sheets = WorkbookFactory.create(file2.getInputStream());
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		Sheet sheet = sheets.getSheetAt(sheetNumber);
		Boolean flag = true;
		ArrayList<String> keyList = new ArrayList<>();
		ArrayList<LinkedHashMap<String, Object>> excelConversionToHashResult = new ArrayList<>();
		LinkedHashMap<String, Object> hashMap = null;
		for (Sheet rows : sheets) {
			for (Row row : rows) {
				int index = 0;
				hashMap = new LinkedHashMap<>();
				for (Cell cell : row) {
					if (flag) {

						keyList.add(cell.getStringCellValue());

					} else {
						if(keyList.size()<=index){
							break;
						}
						String key = keyList.get(index);
						cell.setCellType(CellType.STRING);
						String stringCellValue = cell.getStringCellValue();
						if (stringCellValue != null && !stringCellValue.equals("")) {
							hashMap.put(key, stringCellValue);
						}
//						else {
//							continue;
//						}
						//TODO continue 执行后就不会执行 index++
						index++;
					}
				}
				flag = getaBoolean(flag, keyList, excelConversionToHashResult, hashMap);
			}
		}
		return excelConversionToHashResult;
	}
	/**
	 * 功能描述: 对
	 * @param: InputStream byteArrayInputStream             字节流形式获取需要解析的excle文件
	 * @return: ArrayList<LinkedHashMap < String, Object>>
	 * @auther: SS
	 * @date: 2019/6/11 0011 16:04
	 */
	@Deprecated
	public static ArrayList<LinkedHashMap<String, Object>> excelToHashMap(InputStream byteArrayInputStream) {
		try {
			sheets = WorkbookFactory.create(byteArrayInputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
		Sheet sheet = sheets.getSheetAt(sheetNumber);
		Boolean flag = true;
		ArrayList<String> keyList = new ArrayList<>();
		ArrayList<LinkedHashMap<String, Object>> excelConversionToHashResult = new ArrayList<>();
		for (Row row : sheet) {
			int index = 0;
			LinkedHashMap<String,Object> hashMap = new LinkedHashMap<>();
			for (Cell cell : row) {
				if (flag) {
					keyList.add(cell.getStringCellValue());
				} else {
					String key = keyList.get(index).trim();
					String stringCellValue = null;
					try {
						cell.setCellType(CellType.STRING);
						stringCellValue = cell.getStringCellValue();
					} catch (Exception e) {
						stringCellValue = null;
					}
					hashMap.put(key, stringCellValue);
					index++;
				}
			}
			flag = getaBoolean(flag, keyList, excelConversionToHashResult, hashMap);
		}
		return excelConversionToHashResult;
	}

	//抽取
	private static Boolean getaBoolean(Boolean flag, ArrayList<String> keyList, ArrayList<LinkedHashMap<String, Object>> excelConversionToHashResult, LinkedHashMap<String, Object> hashMap) {
		if (!flag) {
			try {
				String key = keyList.get(0);
				String value = hashMap.get(key).toString();
				if (hashMap.size() != 0) {
					excelConversionToHashResult.add(hashMap);
				}

			} catch (NullPointerException e) {

			}
		}
		flag = false;
		return flag;
	}

	/**
	 * 功能描述:  excel下载
	 * @param: List<?> model                      数据库-模型对象集合
	 *         List<Map<String, String>> list     文件Key与模型对象Key映射
	 *         String fileName                    文件名
	 *         String fileTitle                   文件标题
	 *    用例:
	 *         List<UserModel> userModels = userService.queryUserAll();
	 *         HashMap<String, String> hashMap = new HashMap<>();
	 *         hashMap.put("权限", "role");
	 *         hashMap.put("用户密码", "passWord");
	 *         hashMap.put("用户名称", "userName");
	 *         List<Map<String, String>> list = new ArrayList();
	 *         list.add(hashMap);
	 *         ExcelAnalysisUtil.creatLoadingExcleFile(userModels, list,"朗明智诚","XXX数据");
	 *
	 * @return: void
	 * @auther: SS
	 * @date: 2019/9/20 0020 17:18
	 */
	public static void creatLoadingExcleFile(List<?> model, List<Map<String, String>> list,String fileName,String fileTitle) throws InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException {
		/*获取response对象*/
		ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
		HttpServletResponse response  = requestAttributes.getResponse();
		HttpServletRequest request = requestAttributes.getRequest();
		String userAgent = request.getHeader("User-Agent");
		String shellName = fileName;
		if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
			//IE浏览器处理
			fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
		} else {
			// 非IE浏览器的处理：
			fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
		}
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName + new Date().getTime()+  ".xls");


		/*反射获取实体类属性字段*/
		Class<?> clazz = null;
		if(model!=null||model.size()!=0){
			clazz = model.get(0).getClass();
			clazz.newInstance();
		}else {
			try {
				throw new Exception("model不能为null");
			}catch (Exception e){
				e.printStackTrace();
			}
		}
		String name = clazz.getName();
		Field[] fields = clazz.getDeclaredFields();
		Class<?> superclass = clazz.getSuperclass();
		Field[] superFields = null;
		if(superclass!=null){
			superFields = superclass.getDeclaredFields();
		}
		ArrayList<String> fieldsList = new ArrayList<>();
		//添加子类字段
		for (int i = 0; i < fields.length; i++) {
			String[] split = fields[i].toString().split("\\.");
			fieldsList.add(split[split.length - 1]);
		}
		//添加父类字段
		if(superFields.length>0){
			for (int i = 0; i < superFields.length; i++) {
				String[] split = superFields[i].toString().split("\\.");
				fieldsList.add(split[split.length - 1]);
			}
		}
		/*校验bean属性与MapValue一致性*/
		for (Map<?, ?> stringMap : list) {
			Set<? extends Map.Entry<?, ?>> entries = stringMap.entrySet();
			for (Map.Entry<?, ?> entry : entries) {
				String field = (String) entry.getValue();
				boolean contains = fieldsList.contains(field);
				if(!contains){
					try {
						throw new Exception("形参Map和Bean实体类属性字段不一致,检查一下:"+name+":**"+field+"**,这个字段是不是大小写不一致/直接写错了");
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}

		/* 动态装填数据,构建实体类模型*/
		List<ExcelExportEntity> entity = new ArrayList<>();
		for (Map<?, ?> stringMap : list) {
			for (Map.Entry<?, ?> stringStringEntry : stringMap.entrySet()) {
				String key = (String)stringStringEntry.getKey();
				String value = (String)stringStringEntry.getValue();
				entity.add(new ExcelExportEntity(key, value));
			}
		}
		/*映射封装属性值*/
		list.clear();
		for (Object userModel : model) {
			Map<String, String> describe = BeanUtils.describe(userModel);
			list.add(describe);
		}
		ExportParams exportParams = new ExportParams(fileTitle, shellName);
		Workbook sheets = ExcelExportUtil.exportExcel(exportParams, entity, list);

		ServletOutputStream outputStream = response.getOutputStream();
		sheets.write(outputStream);
		outputStream.flush();
		outputStream.close();
		sheets.close();
	}

	/**
	 *
	 * @param model 数据集合 只能List<Map<String,Object>>>
	 * @param list 字段对应的数据集
	 *   例如:
	 *     map.put("服务器端口号","prot");
	 *     colNameList.add(map);
	 * @param fileName 下载文件名
	 * @param fileTitle
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 * @throws IOException
	 */
	public static void creatLoadingExcleFileByMap(List<Map<String,Object>> model, List<Map<String, String>> list,String fileName,String fileTitle) throws InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException {
		/*获取response对象*/
		ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
		HttpServletResponse response  = requestAttributes.getResponse();
		HttpServletRequest request = requestAttributes.getRequest();
		String userAgent = request.getHeader("User-Agent");
		String shellName = fileName;
		if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
			//IE浏览器处理
			fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
		} else {
			// 非IE浏览器的处理：
			fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
		}
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName + new Date().getTime()+  ".xls");

		/* 动态装填数据,构建实体类模型*/
		List<ExcelExportEntity> entity = new ArrayList<>();
		for (Map<?, ?> stringMap : list) {
			for (Map.Entry<?, ?> stringStringEntry : stringMap.entrySet()) {
				String key = (String)stringStringEntry.getKey();
				String value = (String)stringStringEntry.getValue();
				entity.add(new ExcelExportEntity(key, value));
			}
		}
		ExportParams exportParams = new ExportParams(fileTitle, shellName);
		Workbook sheets = ExcelExportUtil.exportExcel(exportParams, entity, model);

		ServletOutputStream outputStream = response.getOutputStream();
		sheets.write(outputStream);
		outputStream.flush();
		outputStream.close();
		sheets.close();
	}

}
